{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# eICU Collaborative Research Database\n",
    "\n",
    "# Notebook 3: Plot timeseries data for a single patient stay\n",
    "\n",
    "The aim of this notebook is to create a series of plots using timeseries data available for a single patient stay, using the following tables:\n",
    "\n",
    "- `patient`\n",
    "- `vitalperiodic`\n",
    "- `vitalaperiodic`\n",
    "- `lab`\n",
    "\n",
    "Before starting, you will need to install a copy the eICU Collaborative Research Database. For instructions on installing the database, see: .\n",
    "\n",
    "Documentation on the eICU Collaborative Research Database can be found at: http://eicu-crd.mit.edu/."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Getting set up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import psycopg2\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Plot settings\n",
    "%matplotlib inline\n",
    "plt.style.use('ggplot')\n",
    "fontsize = 20 # size for x and y ticks\n",
    "plt.rcParams['legend.fontsize'] = fontsize\n",
    "plt.rcParams.update({'font.size': fontsize})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to the database - which is assumed to be in the current directory\n",
    "fn = 'eicu_demo.sqlite3'\n",
    "con = sqlite3.connect(fn)\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Display a list of tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = \\\n",
    "\"\"\"\n",
    "SELECT type, name\n",
    "FROM sqlite_master \n",
    "WHERE type='table'\n",
    "ORDER BY name;\n",
    "\"\"\"\n",
    "\n",
    "list_of_tables = pd.read_sql_query(query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list_of_tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Selecting a single patient stay "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1. The `patient` table\n",
    "\n",
    "The `patient` table includes general information about the patient admissions (for example, demographics, admission and discharge details). See: http://eicu-crd.mit.edu/eicutables/patient/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# select a single ICU stay\n",
    "patientunitstayid = 141296"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM patient\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "unitstay = pd.read_sql_query(query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# display the first few rows of the dataframe\n",
    "unitstay.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2. The `vitalperiodic` table\n",
    " \n",
    "The `vitalperiodic` table comprises data that is consistently interfaced from bedside vital signs monitors into eCareManager. Data are generally interfaced as 1 minute averages, and archived into the `vitalperiodic` table as 5 minute median values. For more detail, see: http://eicu-crd.mit.edu/eicutables/vitalPeriodic/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM vitalperiodic\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "vitalperiodic = pd.read_sql_query(query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# display the first few rows of the dataframe\n",
    "vitalperiodic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# display a full list of columns\n",
    "vitalperiodic.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# sort the values by the observationoffset (time in minutes from ICU admission)\n",
    "vitalperiodic = vitalperiodic.sort_values(by='observationoffset')\n",
    "vitalperiodic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# subselect the variable columns\n",
    "columns = ['observationoffset','temperature','sao2','heartrate','respiration',\n",
    "          'cvp','etco2','systemicsystolic','systemicdiastolic','systemicmean',\n",
    "          'pasystolic','padiastolic','pamean','st1','st2','st3','icp']\n",
    "\n",
    "vitalperiodic = vitalperiodic[columns].set_index('observationoffset')\n",
    "vitalperiodic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# plot the data\n",
    "figsize = (18,8)\n",
    "title = 'Vital signs (periodic) for patientunitstayid = {} \\n'.format(patientunitstayid)\n",
    "ax = vitalperiodic.plot(title=title, figsize=figsize, fontsize=fontsize,\n",
    "                       marker='o')\n",
    "\n",
    "ax.title.set_size(fontsize)\n",
    "ax.legend(loc='center left', bbox_to_anchor=(1.0, 0.5))\n",
    "ax.set_xlabel(\"Minutes after admission to the ICU\")\n",
    "ax.set_ylabel(\"Absolute value\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- Which variables are available for this patient?\n",
    "- What is the peak heart rate during the period?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3. The `vitalaperiodic` table\n",
    "\n",
    "The `vitalAperiodic` table provides invasive vital sign data that is recorded at irregular intervals. See: http://eicu-crd.mit.edu/eicutables/vitalAperiodic/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM vitalaperiodic\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "vitalaperiodic = pd.read_sql_query(query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# display the first few rows of the dataframe\n",
    "vitalaperiodic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "vitalaperiodic.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# sort the values by the observationoffset (time in minutes from ICU admission)\n",
    "vitalaperiodic = vitalaperiodic.sort_values(by='observationoffset')\n",
    "vitalaperiodic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# subselect the variable columns\n",
    "columns = ['observationoffset','noninvasivesystolic','noninvasivediastolic',\n",
    "          'noninvasivemean','paop','cardiacoutput','cardiacinput','svr',\n",
    "          'svri','pvr','pvri']\n",
    "\n",
    "vitalaperiodic = vitalaperiodic[columns].set_index('observationoffset')\n",
    "vitalaperiodic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# plot the data\n",
    "figsize = (18,8)\n",
    "title = 'Vital signs (aperiodic) for patientunitstayid = {} \\n'.format(patientunitstayid)\n",
    "ax = vitalaperiodic.plot(title=title, figsize=figsize, fontsize=fontsize,\n",
    "                        marker='o')\n",
    "\n",
    "ax.title.set_size(fontsize)\n",
    "ax.legend(loc='center left', bbox_to_anchor=(1.0, 0.5))\n",
    "ax.set_xlabel(\"Minutes after admission to the ICU\")\n",
    "ax.set_ylabel(\"Absolute value\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions\n",
    "\n",
    "- What do the non-invasive variables measure?\n",
    "- How do you think the mean is calculated?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4. The `lab` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query to load data from the patient table\n",
    "query = \\\n",
    "\"\"\"\n",
    "SELECT *\n",
    "FROM lab\n",
    "WHERE patientunitstayid = {}\n",
    "\"\"\".format(patientunitstayid)\n",
    "\n",
    "print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# run the query and assign the output to a variable\n",
    "lab = pd.read_sql_query(query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# display the first few rows of the dataframe\n",
    "lab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# list columns in the table\n",
    "lab.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# sort the values by the offset time (time in minutes from ICU admission)\n",
    "lab = lab.sort_values(by='labresultoffset')\n",
    "lab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# set the index to the offset time\n",
    "lab = lab.set_index('labresultoffset')\n",
    "lab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# subselect the variable columns\n",
    "columns = ['labname','labresult','labmeasurenamesystem']\n",
    "lab = lab[columns]\n",
    "lab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# list the distinct labnames\n",
    "lab['labname'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# pivot the lab table to put variables into columns\n",
    "lab = lab.pivot(columns='labname', values='labresult')\n",
    "lab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# plot laboratory tests of interest\n",
    "labs_to_plot = ['creatinine','pH','Hgb', 'total bilirubin', \n",
    "                'potassium', 'Tacrolimus-FK506', 'WBC x 1000']\n",
    "lab[labs_to_plot].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# plot the data\n",
    "figsize = (18,8)\n",
    "title = 'Laboratory test results for patientunitstayid = {} \\n'.format(patientunitstayid)\n",
    "ax = lab[labs_to_plot].plot(title=title, figsize=figsize, fontsize=fontsize, marker='o',ms=10, lw=0)\n",
    "\n",
    "ax.title.set_size(fontsize)\n",
    "ax.legend(loc='center left', bbox_to_anchor=(1.0, 0.5))\n",
    "ax.set_xlabel(\"Minutes after admission to the ICU\")\n",
    "ax.set_ylabel(\"Absolute value\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
